package com.ingenio.siot.adapter;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.ingenio.siot.Adapter;
import com.ingenio.siot.entity.Permiso;
import com.ingenio.siot.entity.Rol;
import com.ingenio.siot.entity.Usuario;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class RolAdapter extends Adapter{
	
	public List<Rol> devolverTodos() throws Exception{
    	Connection conn = null;
    	List<Rol> roles = new ArrayList<Rol>();
    	PreparedStatement stmt = null;
		try {
			conn = (Connection) abrirConexion();
			String sel ="SELECT * from roles WHERE activo=1";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		ResultSet result = stmt.executeQuery();
    		Rol r = null;
    		
    		while(result.next()){
    			Integer id = result.getInt("IdRol");
    			String desc = result.getString("descRol");
    			boolean activo = result.getBoolean("activo");
    			r = new Rol(id, desc, activo); 
    			roles.add(r);
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return roles;
    }
	
	public Rol devolverRol(Integer idRol) throws Exception{
		Connection conn = null;
		Rol r = null;
		PreparedStatement stmt = null;
		try{
			conn = (Connection) abrirConexion();
			String query = "SELECT * FROM roles WHERE IdRol=? and activo=1";
			stmt = (PreparedStatement) conn.prepareStatement(query);
			stmt.setInt(1, idRol);
			ResultSet result = stmt.executeQuery();
			if(result.next()){
				Integer id = result.getInt("IdRol");
				String desc = result.getString("descRol");
				boolean activo = result.getBoolean("activo");
				r = new Rol(id, desc, activo); 
			}
		}
		catch (Exception e){
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos", e);
		}
		finally{
			cerrarConexion(conn);
		}
		return r;
	}
	
	public Rol crearRol(Rol rol) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		RolAdapter ra = new RolAdapter();
		Rol r = null;
    	try {
    		conn = (Connection) abrirConexion();
    		String query ="INSERT INTO roles(descRol,activo) VALUES(?,?)";
    		stmt = (PreparedStatement) conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    		stmt.setString(1,rol.getDescRol());
    		stmt.setBoolean(2,rol.isActivo());
    		stmt.execute();
    		ResultSet id = stmt.getGeneratedKeys();
    		if (id.next()){
    			r = ra.devolverRol(id.getInt(1));
    		}
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return r;
	}
	
	public void editarRol(Rol rol) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
    		conn = (Connection) abrirConexion();
    		String query =	"UPDATE roles " +
    						"SET descRol=?, activo=? " +
    						"WHERE IdRol=?";
    		stmt = (PreparedStatement) conn.prepareStatement(query);
    		stmt.setString(1,rol.getDescRol());
    		stmt.setBoolean(2,rol.isActivo());
    		stmt.setInt(3,rol.getIdRol());
    		stmt.execute();
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
	}
	
	public void eliminarRol(Integer idRol) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
    	try {
    		conn = (Connection) abrirConexion();
    		String query =  "UPDATE roles SET activo=0 WHERE IdRol=?";
    		stmt = (PreparedStatement) conn.prepareStatement(query);
    		stmt.setInt(1,idRol);
    		stmt.execute();
    	}
    	catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
	}
	
	public List<Rol> devolverRolesUsuario(Usuario usu) throws Exception{
    	Connection conn = null;
    	List<Rol> roles = new ArrayList<Rol>();
    	PreparedStatement stmt = null;
    	RolAdapter ra = new RolAdapter();
		try {
			conn = (Connection) abrirConexion();
			String sel ="SELECT * from usuarios_roles WHERE IdUsuario=?";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, usu.getIdUsuario());
    		ResultSet result = stmt.executeQuery();
    		Rol r = null;
    		
    		while(result.next()){
    			Integer idRol = result.getInt("IdRol");
    			r = ra.devolverRol(idRol); 
    			if (r.isActivo()){
    				roles.add(r);
    			}
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return roles;
    }
	
	public List<Rol> devolverRolesPermiso(Permiso per) throws Exception{
    	Connection conn = null;
    	List<Rol> roles = new ArrayList<Rol>();
    	PreparedStatement stmt = null;
    	RolAdapter ra = new RolAdapter();
		try {
			conn = (Connection) abrirConexion();
			String sel ="SELECT * from roles_permisos WHERE IdPermiso=?";
    		stmt = (PreparedStatement) conn.prepareStatement(sel);
    		stmt.setInt(1, per.getIdPermiso());
    		ResultSet result = stmt.executeQuery();
    		Rol r = null;
    		
    		while(result.next()){
    			Integer idRol = result.getInt("IdRol");
    			r = ra.devolverRol(idRol); 
    			if (r.isActivo()){
    				roles.add(r);
    			}
    		}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		cerrarConexion(conn);
    	}
    	return roles;
    }

	public void crearRolesPermisos(List<Rol> roles) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		try{
			conn = (Connection) abrirConexion();
			conn.setAutoCommit(false);
			String query ="INSERT into roles_permisos(IdRol,IdPermiso) VALUES(?,?)";
    		stmt = (PreparedStatement) conn.prepareStatement(query);
			if (this.eliminarRolesPermisos(conn)){
				for(Rol r:roles){
					for(Permiso p:r.getPermisosRol()){
						stmt.setInt(1, r.getIdRol());
			    		stmt.setInt(2, p.getIdPermiso());
			    		stmt.addBatch();
					}
				}
				stmt.executeBatch();
			}else{
				conn.rollback(); // error al eliminar
			}
		}
		catch (Exception e) {
			e.printStackTrace();
			conn.rollback();
			throw new Exception("Error al conectar con la base de datos.", e);
		}
    	finally{
    		conn.commit();
    		cerrarConexion(conn);
    	}
	}
	
	public boolean eliminarRolesPermisos(Connection conn) throws Exception{
		PreparedStatement stmt = null;
		try{
			// elimino todos los registros
			String sql ="DELETE from roles_permisos";
    		stmt = (PreparedStatement) conn.prepareStatement(sql);
    		stmt.execute();
		}
		catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
}
